package com.DreamTeam.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.DreamTeam.dao.ICustomerDao;
import com.DreamTeam.po.Customer;
import com.DreamTeam.po.Employee;
import com.DreamTeam.po.User;

public class CustomerDao implements ICustomerDao {

	private Connection conn = null;

	public CustomerDao(Connection conn) {
		this.conn = conn;
	}

	@Override
	public boolean doAdd(Customer customer) {
		// TODO Auto-generated method stub
		String sql = "insert into customer (CustomerNo,CustomerName,Telephone,Address,zip) values(?,?,?,?,?)";
		PreparedStatement prsmt = null;
		int flag = 0;
		try {
			prsmt = conn.prepareStatement(sql);
			prsmt.setString(1, customer.getCustomerNo());
			prsmt.setString(2, customer.getCustomerName());
			prsmt.setString(3, customer.getTelephone());
			prsmt.setString(4, customer.getAddress());
			prsmt.setString(5, customer.getZip());
			flag = prsmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (flag > 0)
			return true;
		else
			return false;
	}

	@Override
	public boolean doUpdate(Customer customer) {
		// TODO Auto-generated method stub
		String sql = "update customer set CustomerNo = ?,CustomerName =?,Telephone =?,Address =?,zip =? where CustomerNo = ?";
		PreparedStatement prsmt = null;
		int flag = 0;
		try {
			prsmt = conn.prepareStatement(sql);
			prsmt.setString(1, customer.getCustomerNo());
			prsmt.setString(2, customer.getCustomerName());
			prsmt.setString(3, customer.getTelephone());
			prsmt.setString(4, customer.getAddress());
			prsmt.setString(5, customer.getZip());
			prsmt.setString(6, customer.getCustomerNo());
			flag = prsmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (flag > 0)
			return true;
		return false;
	}

	@Override
	public boolean doDelete(Customer customer) {
		// TODO Auto-generated method stub
		String sql = "delete from customer where customerNo = ?";
		PreparedStatement prsmt = null;
		int flag = 0;
		try {
			prsmt = conn.prepareStatement(sql);
			prsmt.setString(1, customer.getCustomerNo());
			flag = prsmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (flag > 0)
			return true;
		return false;
	}

	@Override
	public List<Customer> queryCustomerAll() {
		// TODO Auto-generated method stub
		String sql = "select * from customer";
		List<Customer> list = new ArrayList();
		PreparedStatement prsmt = null;
		try {
			prsmt = conn.prepareStatement(sql);
			ResultSet re = prsmt.executeQuery();
			while (re.next()) {
				Customer customer = new Customer();
				customer.setCustomerNo(re.getString(1));
				customer.setCustomerName(re.getString(2));
				customer.setTelephone(re.getString(3));
				customer.setAddress(re.getString(4));
				customer.setZip(re.getString(5));
				list.add(customer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Customer> queryCustomerByColumn(String keyword,
			String columnName, String orderColumn, String model) {
		String sql = "select * from customer where " + columnName
				+ " like ? order by " + orderColumn + " " + model;
		PreparedStatement prsmt = null;
		List<Customer> list = new ArrayList();
		try {
			prsmt = conn.prepareStatement(sql);
			prsmt.setString(1, "%" + keyword + "%");
			ResultSet re = prsmt.executeQuery();
			while (re.next()) {
				Customer customer = new Customer();
				customer.setCustomerNo(re.getString(1));
				customer.setCustomerName(re.getString(2));
				customer.setTelephone(re.getString(3));
				customer.setAddress(re.getString(4));
				customer.setZip(re.getString(5));
				list.add(customer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Customer> queryOrderByColumnName(String columnName, String model) {
		String sql = "select * from customer order by " + columnName + " "
				+ model;
		PreparedStatement prsmt = null;
		List<Customer> list = new ArrayList();
		try {
			prsmt = conn.prepareStatement(sql);
			ResultSet re = prsmt.executeQuery();
			while (re.next()) {
				Customer customer = new Customer();
				customer.setCustomerNo(re.getString(1));
				customer.setCustomerName(re.getString(2));
				customer.setTelephone(re.getString(3));
				customer.setAddress(re.getString(4));
				customer.setZip(re.getString(5));
				list.add(customer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Customer> queryByKeyword(String keyword) {
		String sql = "select * from customer where customerNo like ? or customerName like ?" +
				" or telephone like ? or address like ? or zip like ?";
		PreparedStatement prsmt = null;
		List<Customer> list = new ArrayList();
		try {
			prsmt = conn.prepareStatement(sql);
			prsmt.setString(1, "%"+keyword+"%");
			prsmt.setString(2, "%"+keyword+"%");
			prsmt.setString(3, "%"+keyword+"%");
			prsmt.setString(4, "%"+keyword+"%");
			prsmt.setString(5, "%"+keyword+"%");
			ResultSet re = prsmt.executeQuery();
			while (re.next()) {
				Customer customer = new Customer();
				customer.setCustomerNo(re.getString(1));
				customer.setCustomerName(re.getString(2));
				customer.setTelephone(re.getString(3));
				customer.setAddress(re.getString(4));
				customer.setZip(re.getString(5));
				list.add(customer);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return list;
	}

}
